package domain.web.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import domain.web.dao.ManufacturerDao;
import domain.web.model.Manufacturer;

@Repository("manufacturerDao")
public class ManufacturerDaoImpl extends JdbcDaoSupport implements
		ManufacturerDao {

	@Autowired
	private DataSource dataSource;

	@PostConstruct
	void init() {
		setDataSource(dataSource);
	}

	@Override
	public long insert(final Manufacturer manufacturer) {
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		final PreparedStatementCreator psc = new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement pstmt = con.prepareStatement(
						"INSERT INTO Manufacturers(Name, Uri) VALUES(?,?)",
						Statement.RETURN_GENERATED_KEYS);
				pstmt.setString(1, manufacturer.getName());
				pstmt.setString(2, manufacturer.getUri());
				return pstmt;
			}
		};
		getJdbcTemplate().update(psc, keyHolder);
		return keyHolder.getKey().longValue();
	}

	@Override
	public void update(Manufacturer manufacturer) {
		String sql = "UPDATE Manufacturers SET Name = ?, Uri = ? WHERE Id = ?";
		getJdbcTemplate().update(sql, manufacturer.getName(),
				manufacturer.getUri(), manufacturer.getId());
	}

	@Override
	public void delete(long id) {
		String sql = "DELETE FROM Manufacturers WHERE Id = ?";
		getJdbcTemplate().update(sql, id);
	}

	@Override
	public Manufacturer get(long id) {
		String sql = "SELECT * FROM Manufacturers WHERE Id = ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				id);
		return results.isEmpty() ? null : rowMapper(results.get(0));
	}

	@Override
	public Manufacturer get(String name) {
		String sql = "SELECT * FROM Manufacturers WHERE Name = ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				name);
		return results.isEmpty() ? null : rowMapper(results.get(0));
	}
	
	@Override
	public Manufacturer getByUri(String uri) {
		String sql = "SELECT * FROM Manufacturers WHERE Uri = ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				uri);
		return results.isEmpty() ? null : rowMapper(results.get(0));
	}

	@Override
	public List<Manufacturer> getAll() {
		String sql = "SELECT * FROM Manufacturers ORDER BY Name";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql);
		List<Manufacturer> manufacturers = new ArrayList<Manufacturer>();
		for (Map<String, Object> map : results) {
			manufacturers.add(rowMapper(map));
		}
		return manufacturers;
	}

	private Manufacturer rowMapper(Map<String, Object> row) {
		Manufacturer manufacturer = new Manufacturer();
		manufacturer.setId((Long) row.get("Id"));
		manufacturer.setName((String) row.get("Name"));
		manufacturer.setUri((String) row.get("Uri"));
		manufacturer.setLabel((String) row.get("Name"));
		return manufacturer;
	}
}
